﻿
CREATE PROCEDURE [dbo].[resuma_CalculStatPlata] 
	@Anul smallint,
	@Luna smallint,
	@CodUnitate smallint,
	@IDPersoanaSelectata int
AS

DECLARE @MesajEroare varchar(500)

-----pozitii pe angajat si pozitie salarizare
	INSERT INTO [AngajatiStatPlata]
     ([IDPersoana],[IDAngajatSalarizare],[Suma],[IDTipStatPlata],[Anul],[Luna])      
     SELECT Ps.IDPersoana,Ans.IDAngajatSalarizare,0, TSp.IDTipStatPlata,@Anul,@Luna
     FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [TipStatPlata] TSp ON TSp.Cod IN ('BR')
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) 
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND 
		NOT EXISTS(Select * FROM [AngajatiStatPlata] APS 
				   WHERE [IDPersoana]= Ps.IdPersoana AND IDAngajatSalarizare=Ans.IDAngajatSalarizare AND TSp.IDTipStatPlata = IDTipStatPlata)
	GROUP BY  Ps.IDPersoana,Ans.IDAngajatSalarizare,TSp.IDTipStatPlata

	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la completare pontaje'
		GOTO EROARE2				   
	END	


-----pozitii pe angajat si pozitie salarizare
	INSERT INTO [AngajatiStatPlata]
     ([IDPersoana],[IDAngajatSalarizare],[Suma],[IDTipStatPlata],[Anul],[Luna])      
     SELECT Ps.IDPersoana,Ans.IDAngajatSalarizare,0, TSp.IDTipStatPlata,@Anul,@Luna
     FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     JOIN [TipStatPlata] TSp ON TSp.Cod IN ('BRT')
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) AND Ans.NormaBaza=1
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND 
		NOT EXISTS(Select * FROM [AngajatiStatPlata] APS 
				   WHERE [IDPersoana]= Ps.IdPersoana AND IDAngajatSalarizare=Ans.IDAngajatSalarizare AND TSp.IDTipStatPlata = IDTipStatPlata)
	 GROUP BY  Ps.IDPersoana,Ans.IDAngajatSalarizare,TSp.IDTipStatPlata 

	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la completare pontaje'
		GOTO EROARE2				   
	END	

				 
--sterge pozitiile eliminate

DELETE FROM [AngajatiStatPlata] WHERE IDTipStatPlata in (SELECT IDTipStatPlata FROM [TipStatPlata] WHERE COD IN ('BR','BRT')) AND
	NOT EXISTS(SELECT * FROM
     [Persoane] Ps
     JOIN [AngajatiSalarizare] AnS ON Ps.IdPersoana = Ans.IdPersoana
     WHERE 
        (AnS.PanaLaData IS NULL OR @Anul*100+@Luna<=YEAR(Ans.PanaLaData)*100+MONTH(Ans.PanaLaData))
		AND (Ps.DataPlecarii IS NULL OR @Anul*100+@Luna<=YEAR(Ps.DataPlecarii)*100+MONTH(Ps.DataPlecarii))
		AND (Ps.CodUnitate=@CodUnitate) 
		AND(@IDPersoanaSelectata=-1 OR Ps.IDPersoana = @IDPersoanaSelectata) AND
		[AngajatiStatPlata].[IDPersoana]= Ps.IdPersoana AND [AngajatiStatPlata].IDAngajatSalarizare=Ans.IDAngajatSalarizare )


	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la stergere inexistente'
		GOTO EROARE2				   
	END	



	IF @@ERROR<>0
	BEGIN
		set @MesajEroare = 'Eroare la update sporuri'
		GOTO EROARE2				   
	END	

UPDATE [AngajatiStatPlata] SET 
	[Suma] = (SELECT Coalesce(SUM(Suma),0) FROM [AngajatiSume] ASum WHERE [AngajatiStatPlata].IDPersoana=Asum.IDPersoana AND
		[AngajatiStatPlata].IDAngajatSalarizare = Asum.IDAngajatSalarizare AND [AngajatiStatPlata].Luna = ASum.Luna
		AND [AngajatiStatPlata].Anul = ASum.Anul)
FROM TipStatPlata tsp		
WHERE  Luna = @Luna AND Anul=@Anul AND [AngajatiStatPlata].IDTipStatPlata = tsp.IDTipStatPlata
AND tsp.COD IN ('BR') 

UPDATE [AngajatiStatPlata] SET 
	[Suma] = (SELECT Coalesce(SUM(Suma),0) FROM [AngajatiStatPlata] ASum JOIN [TipStatPlata] tsp ON tsp.IDTipStatPlata=ASum.IDTipStatPlata
	 WHERE [AngajatiStatPlata].IDPersoana=Asum.IDPersoana AND
		tsp.COD IN ('BR')  AND [AngajatiStatPlata].Luna = ASum.Luna
		AND [AngajatiStatPlata].Anul = ASum.Anul)
FROM TipStatPlata tsp		
WHERE  Luna = @Luna AND Anul=@Anul AND [AngajatiStatPlata].IDTipStatPlata = tsp.IDTipStatPlata
AND tsp.COD IN ('BRT') 




GOTO Gata
EROARE:
	RAISERROR ('Eroare la recalcularea salariilor!',16,-1)
	GOTO Gata
EROARE1:
	RAISERROR ('!!',16,-1)	
	GOTO Gata
EROARE2:
	RAISERROR (@MesajEroare,16,5)	
	GOTO Gata
	
Gata: